#encoding: utf-8
from .startDB import sparrowDB
from .getLogs import collectLogs
import traceback

def ExecuteSql(choice, param1='', param2='0', param3='', param4='', param5=''):
    try:
        # 用例管理筛选条件查询
        if choice == 0:
            param1 = '%' + param1 + '%'
            if param3 != '':
                sql = '''SELECT * FROM case_manage WHERE case_id = '%s' AND isDel = 0'''%(param3,)
            elif param4 != '' or param5 != '':
                param4 = '%' + param4 + '%'
                param5 = '%' + param5 + '%'
                sql = '''SELECT * FROM case_manage WHERE case_path like '%s' AND case_parm like '%s' AND isDel = 0 ORDER BY create_at DESC''' % (param4, param5)
            else:
                if param1 == '%%' and param2 == '0':
                    sql = '''SELECT * FROM case_manage WHERE isDel = 0 ORDER BY create_at DESC'''
                elif param1 != '%%' and param2 == '0':
                    sql = '''SELECT * FROM case_manage WHERE case_name LIKE '%s' AND isDel = 0 ORDER BY create_at DESC'''%(param1,)
                else:
                    sql = '''SELECT * FROM case_manage WHERE case_name LIKE '%s' AND group_id = '%s' AND isDel = 0 ORDER BY create_at DESC'''%(param1,param2)
            db_res = sparrowDB.get_all(sql)
            return db_res

        # 执行结果筛选条件查询
        if choice == 1:
            param1 = '%' + param1 + '%'
            if param3 == '':  # query_res_status为第三个参数，但是默认为0
                param3 = '0'

            if param3 == '1':
                param3_list = '('+"1"+')'
            elif param3 == '2':
                param3_list = '('+"9"+')'
            elif param3 == '3':
                param3_list = '('+'0, 2, 3, 4'+')'
            else:
                param3_list = '('+'0, 1, 2, 3, 4, 9'+')'

            # 设置查询最近3小时内的数据中的最新数据的sql条件
            tem_sql = '''
                    wheel_no IN (
                        SELECT DISTINCT(a.wheel_no) FROM 
                        case_run_res a INNER JOIN (
                            SELECT res_plan_id, MAX(create_at) create_at FROM 
                            case_run_res
                            WHERE create_at >= FROM_UNIXTIME((UNIX_TIMESTAMP(NOW()) -3600 * 3), '%Y-%m-%d %H:%i:s')
                             GROUP BY res_plan_id
                        ) b ON a.res_plan_id = b.res_plan_id AND a.create_at = b.create_at
                        WHERE a.create_at >= FROM_UNIXTIME((UNIX_TIMESTAMP(NOW()) -3600  * 3), '%Y-%m-%d %H:%i:s')
                    ) AND  create_at >= FROM_UNIXTIME((UNIX_TIMESTAMP(NOW()) -3600  * 3), '%Y-%m-%d %H:%i:s')
                    ORDER BY create_at DESC
            '''
            # 不设置任何查询条件的查询sql（用下面第三个sql也可以实现相同的查询结果，但是第三个多了两个筛选条件，考虑到效率做了该判断）
            if param1 == '%%' and param2 == '0' and param3 == '0':
                sql = '''SELECT * FROM case_run_res WHERE %s''' %(tem_sql,)

            # 只设置用例名称，不设置所属计划，查询sql
            elif param1 != '%%' and param2 == '0' and param3 == '0':
                sql = '''
                SELECT * FROM case_run_res WHERE res_case_id IN (SELECT case_id FROM case_manage WHERE case_name LIKE '%s') AND %s
                ''' %(param1, tem_sql)
            # 设置了所属计划的查询sql
            elif param2 == '0' and param3 != '0':
                sql = '''
                SELECT * FROM case_run_res WHERE res_case_id IN (SELECT case_id FROM case_manage WHERE case_name LIKE '%s') AND ''' % (param1,) + ''' res_status IN ''' + param3_list + ''' AND %s''' %(tem_sql,)
            else:
                sql = '''
                SELECT * FROM case_run_res WHERE res_case_id IN (SELECT case_id FROM case_manage WHERE case_name LIKE '%s') AND res_plan_id = '%s' AND ''' % (param1, param2) +''' res_status IN '''+ param3_list+ ''' AND %s''' %(tem_sql,)
            db_res = sparrowDB.get_all(sql)
            return db_res

        # 执行结果总结数据查询
        if choice == 2:
            sql_summary = '''
                            SELECT c.s_plan_name, c.succ_count, c.fail_count, c.create_at FROM case_run_res_summary c
                            INNER JOIN
                            (SELECT s_plan_id, MAX(create_at) create_at FROM case_run_res_summary WHERE run_type = '1' GROUP BY s_plan_id) a
                            ON a.s_plan_id = c.s_plan_id AND c.create_at = a.create_at WHERE c.succ_count != 0 OR c.fail_count != 0 ORDER BY create_at DESC
                            '''
            db_res = sparrowDB.get_all(sql_summary)
            return db_res
        # if db_res:
        #     collectLogs(loginfo='sql[%s]执行成功' % (sql,), logger='ExecuteSql')
        # else:
        #     collectLogs(loginfo='sql[%s]查询结果为空，执行结果：%s' % (sql, db_res), logger='ExecuteSql')
    except:
        collectLogs(loginfo='sql执行遇到异常，原因：%s' % (traceback.format_exc(),), lever='error', logger='ExecuteSql')
        return False






